package org.jeecg.modules.internship.service.impl;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.Units;
import org.apache.poi.xwpf.usermodel.*;
import org.jeecg.common.system.query.QueryGenerator;
import org.jeecg.common.util.DateUtils;
import org.jeecg.modules.education.service.EducationService;
import org.jeecg.modules.internship.entity.Internship;
import org.jeecg.modules.internship.entity.vo.InternshipVo;
import org.jeecg.modules.internship.entity.vo.QureyInternship;
import org.jeecg.modules.internship.mapper.InternshipMapper;
import org.jeecg.modules.internship.service.InternshipService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.jeecg.modules.nation.service.NationService;
import org.jeecg.modules.politics.service.PoliticsStatusService;
import org.jeecg.modules.school.service.SciSchoolService;
import org.jeecgframework.poi.excel.ExcelExportUtil;
import org.jeecgframework.poi.excel.ExcelImportUtil;
import org.jeecgframework.poi.excel.entity.ExportParams;
import org.jeecgframework.poi.excel.entity.ImportParams;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTcPr;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTVMerge;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STMerge;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STVerticalJc;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ResourceUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigInteger;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLDecoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.UUID;
import java.util.stream.Collectors;

/**
 *
 * @author liujiamiao
 * @email 2544296058@qq.com
 * @since 2020-10-21
 */
@Slf4j
@Service
public class InternshipServiceImpl extends ServiceImpl<InternshipMapper, Internship> implements InternshipService {

    @Autowired
    private NationService nationService;

    @Autowired
    private PoliticsStatusService politicsStatusService;

    @Autowired
    private EducationService educationService;

    @Autowired
    private SciSchoolService sciSchoolService;
    /**
     * 分页多条件查询
     * @param page
     * @param limit
     * @param query
     * @return
     */
    @SneakyThrows
    @Override
    public IPage<Internship> pageSelect(Integer page, Integer limit, QureyInternship query) {
        log.info("query = {}",query);
        Page<Internship> pageList = new Page<>(page,limit);
        QueryWrapper wrapper = new QueryWrapper();

        if(query!=null){
            String name = query.getName();
            String start = query.getStart();
            String end = query.getEnd();
            String key = query.getKey();
            String value = query.getValue();
            //名字查询
            if(!StringUtils.isEmpty(name)){
                wrapper.eq("name",name);
            }

            //实习时间查询
            if(!StringUtils.isEmpty(start)&&!StringUtils.isEmpty(end)){
                start = DateUtils.addDate(start,1000*60*60*24);
                end = DateUtils.addDate(end,1000*60*60*24);
                //增加一天时间
                wrapper.between("start_date",start,end);
            }
            //条件选择查询
            if(!StringUtils.isEmpty(key)&&!StringUtils.isEmpty(value)){
                //由于前台传过来的是值，而后台存储的是id，因此需要转换为id
                if(key.equals("education")){
                    Integer id = null;
                    try {
                        id = educationService.getIdByName(value);
                    } catch (Exception e) {
                        log.warn("灭有获取到信息");
                        id = 0;
                    }
                    wrapper.like(key,id);
                }else if(key.equals("sex")){
                    if("男".equals(value)||"男生".equals(value)){
                        wrapper.like(key,1);
                    }else if("女".equals(value)||"女生".equals(value)){
                        wrapper.like(key,2);
                    }else {
                        wrapper.like(key,null);
                    }
                } else{
                    wrapper.like(key,value);
                }
            }
        }

        QueryGenerator.installAuthMplus(wrapper, Internship.class);

        IPage iPage = this.page(pageList, wrapper);

        //学校编码转为学校名称
        List<Internship> records = iPage.getRecords();
        for (Internship entity : records) {
            String schoolName = sciSchoolService.querySchoolName(entity.getSchool());
            entity.setSchool(schoolName);
        }

        return iPage;
    }

    /**
     * 导出实习生基本信息以excel方式
     */
    @Override
    public String excelExport() throws IOException {
        QueryWrapper<Internship> wrapper = new QueryWrapper<>();
        //过滤数据（添加数据权限）
        QueryGenerator.installAuthMplus(wrapper, Internship.class);
        List<Internship> list = this.list(wrapper);
        List<InternshipVo> internshipVoList = list.stream().map(entity -> {
            //返回的是InternshipVo实体类
            InternshipVo iv = new InternshipVo();
            //将属性值赋值到iv中
            BeanUtils.copyProperties(entity, iv);
            //将民族、政治面貌和学历 的id获取到相对于的name
            iv.setNationString(nationService.getById(entity.getNation()).getNationName());
            iv.setPoliticsStatusString(politicsStatusService.getById(entity.getPoliticsStatus()).getPoliticsName());
            iv.setEducationString(educationService.getById(entity.getEducation()).getEducationName());
            return iv;
        }).collect(Collectors.toList());

        //判断该目录是否存在，不存在则创建
//        String path= ResourceUtils.getURL("classpath:").getPath()+"upload/excelUpload/excels/";
//        path = URLDecoder.decode(path, "utf-8");

        //再dev配置文件中，设置为本地存储，在mvc配置文件中以及映射了到"D:\\upFiles\\"
        //只需要访问"http://localhost:8080/jeecg-boot/excels/0577d49053324acc9bd3115d1dd700f3excel.xlsx"即可
        String path = "D:\\upFiles\\excels\\";
        File file = new File(path);
        if (!file.exists()) {
            file.mkdir();
        }

        //导出数据
        String uuid = UUID.randomUUID().toString().replace("-", "");
        String localPath = path+uuid+"excel.xlsx";
        Workbook wb = ExcelExportUtil.exportExcel(new ExportParams("实习生档案", "实习生基本信息"),InternshipVo.class,internshipVoList);
        FileOutputStream fos = new FileOutputStream(localPath);
        wb.write(fos);
        fos.close();

        //返回相对地址，项目地址由request获取
        return "excels/"+uuid+"excel.xlsx";
    }

    /**
     * 导入数据
     * @param inputStream
     * @throws Exception
     */
    @Override
    public void excelImport( InputStream inputStream) throws Exception {

        ImportParams params = new ImportParams();
        params.setTitleRows(1); //表示的是表格标题行数，如果没有就是0，如果有一个标题就是1，如果是两个标题就2
        params.setHeadRows(1); //表示的是表头行数，默认是1，如果有两个表头则需要设置2。
        params.setNeedSave(true);
        //转为实体类集合
        List<InternshipVo> list = ExcelImportUtil.importExcel(inputStream, InternshipVo.class, params);
        log.info("封装前的数据 = {}",list);

        //判断数据库里是否存在这些数据，存在的都不保存
        List<Internship> isExists = baseMapper.selectList(null);

        //遍历封装数据
        list.stream().map(entity->{
            Internship internship = new Internship();
            //将entity的属性复制到internship对象中
            BeanUtils.copyProperties(entity,internship);
            internship.setNation(nationService.getIdByName(entity.getNationString()));
            internship.setPoliticsStatus(politicsStatusService.getIdByName(entity.getPoliticsStatusString()));
            internship.setEducation(educationService.getIdByName(entity.getEducationString()));
            return internship;
        }).forEach(entity->{
            //这样做应该可以减轻数据库重复查询的负担
            if (isExists.contains(entity)) {
                //根据身份证更新数据
                updateBySId(entity);
            }else{
                //添加数据
                baseMapper.insert(entity);
            }
        });

        //批量插入internshipList
//        log.info("总共 = {}条数据",internshipList.size());
//        this.saveBatch(internshipList);

    }


    /**
     * 根据身份证更新数据
     * @param internship
     * @return
     */
    @Override
    public Integer updateBySId(Internship internship) {
        QueryWrapper wrapper = new QueryWrapper();
        wrapper.eq("s_Id",internship.getSfzId());
        int update = baseMapper.update(internship, wrapper);
        return update;
    }

    /**
     * 生成word，并返回下载链接
     * @param id
     * @return
     */
    @Override
    public String downLoadWord(Integer id) throws IOException, InvalidFormatException {

        Internship internship = this.getById(id);

        XWPFDocument doc = new XWPFDocument();
        XWPFParagraph para;
        XWPFRun run;
        String[][] title = new String[][]{
                {"个人数据模板","","",""},
                {"个人信息","","",""},
                {"姓名"," ",""," "},
                {"抖音账号"," ",""," "},
                {"性别"," ",""," "},
                {"身份证号"," ",""," "},
                {"年龄"," ","出生日期"," "},
                {"民族"," ","政治面貌"," "},
                {"开户银行"," ","银行卡号"," "},
                {"实习开始时间"," ","实习结束时间"," "},
                {"联系方式","","",""},
                {"紧急联系人"," ","紧急联系人电话"," "},
                {"邮箱"," ","手机号"," "},
                {"QQ"," ","微信"," "},
                {"电子设备信息","","",""},
                {"有线网卡"," ","无线网卡"," "},
                {"其他信息","","",""},
                {"籍贯"," ","居住地"," "},
                {"所属学校"," ","学历"," "},
        };
        //创建表格
        XWPFTable table  = doc.createTable(title.length,4);
        table.setCellMargins(3, 5, 3, 5);
//        table.addNewCol();//添加新列
//        table.createRow();//添加新行

        XWPFTableRow row;
        XWPFTableCell cell;
        CTTcPr cellPr;

        Integer count = 0;

        for(int j = 0;j<title.length;j++){
            row = table.getRow(j);
            if(j==0){
                row.setHeight(1000);
            }else{
                row.setHeight(600);
            }
            for(int i=0;i<title[j].length;i++){
                //创建单元格
                cell = row.getCell(i);
                //单元格属性
                cellPr = cell.getCTTc().addNewTcPr();
                //设置宽度
                cellPr.addNewTcW().setW(BigInteger.valueOf(8500));
                //垂直居中
                cellPr.addNewVAlign().setVal(STVerticalJc.CENTER);
                //第一个段落
                para = cell.getParagraphs().get(0);
                //设置对齐方式
                para.setAlignment(ParagraphAlignment.CENTER);
                //段落的对齐方式 1左 2中 3右 4往上 左 不可写0和负数
                para.setFontAlignment(2);
                //一个XWPFRun代表具有相同属性的一个区域：一段文本
                run = para.createRun();
                //设置字体
                run.setFontFamily("仿宋");
                //设置字体大小
                run.setFontSize(11);

                if(j==0){
                    if(i==0){
                        cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.RESTART);
                        run = para.createRun();
                        run.setFontSize(18);
                        run.setText(title[j][i]);
                    }else{
                        cell = row.getCell(i);
                        cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.CONTINUE);
                    }
                }else{
                    if (j==1||j==10||j==16||j==14){
                        //设置对齐方式
                        para.setAlignment(ParagraphAlignment.LEFT);
                        if(i==0) {
                            cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.RESTART);
                            run = para.createRun();
                            run.setFontSize(13);
                            run.setText(title[j][i]);
                        }else{
                            cell = row.getCell(i);
                            cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.CONTINUE);
                        }
                    }else{
                        if(i==0||(i==2&&(j<2||j>5))) {
                            run = para.createRun();
                            run.setFontSize(11);
                            run.setText(title[j][i]);
                        }else if((j>=2&&j<=5)&&(i==2||i==3)){
                            //图片
                            if(i==2&&j==2) {
                                String uri = internship.getIcon();
                                if(uri==null){
                                    continue;
                                }
                                //创建单元格
                                cell = row.getCell(i);
                                //第一个段落
                                para = cell.getParagraphs().get(0);
                                //设置对齐方式
                                para.setAlignment(ParagraphAlignment.CENTER);
                                //段落的对齐方式 1左 2中 3右 4往上 左 不可写0和负数
                                para.setFontAlignment(2);
                                //一个XWPFRun代表具有相同属性的一个区域：一段文本
                                run = para.createRun();
                                //设置字体
                                run.setFontFamily("仿宋");
                                //设置字体大小
                                run.setFontSize(11);

                                URL url = new URL(uri);
                                HttpURLConnection conn = (HttpURLConnection) url.openConnection();
                                conn.setRequestMethod("GET");
                                conn.setConnectTimeout(5 * 1000);
                                InputStream is = conn.getInputStream();// 通过输入流获取图片数据
                                if(is==null){
                                    continue;
                                }
                                List<XWPFParagraph> paragraphs = cell.getParagraphs();
                                XWPFParagraph newPara = paragraphs.get(0);
                                XWPFRun imageCellRunn = newPara.createRun();
                                imageCellRunn.addPicture(is, XWPFDocument.PICTURE_TYPE_JPEG, "icon", Units.toEMU(120), Units.toEMU(120)); // 200x200 pixels

                            } else if(j==5&&i==3){
                                //行合并
                                table.getRow(2).getCell(2).getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.RESTART);
                                table.getRow(3).getCell(2).getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.CONTINUE);
                                table.getRow(4).getCell(2).getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.CONTINUE);
                                table.getRow(5).getCell(2).getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.CONTINUE);

                                //列合并
                                table.getRow(2).getCell(2).getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.RESTART);
                                table.getRow(2).getCell(3).getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.CONTINUE);
                                table.getRow(3).getCell(2).getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.RESTART);
                                table.getRow(3).getCell(3).getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.CONTINUE);
                                table.getRow(4).getCell(2).getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.RESTART);
                                table.getRow(4).getCell(3).getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.CONTINUE);
                                table.getRow(5).getCell(2).getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.RESTART);
                                table.getRow(5).getCell(3).getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.CONTINUE);

                            }
                        } else{
                            switch (count){
                                //对应的属性填到对应的单元格上
                                case 0:{
                                    run.setText(internship.getName());
                                    break;
                                }
                                case 1:{
                                    run.setText(internship.getDouyin());
                                    break;
                                }
                                case 2:{
                                    run.setText(internship.getSex()==1?"男":"女");
                                    break;
                                }
                                case 3:{
                                    run.setText(internship.getSfzId());
                                    break;
                                }
                                case 4:{
                                    run.setText(String.valueOf(internship.getAge()));
                                    break;
                                }
                                case 5:{
                                    Date date = internship.getBirthDate();
                                    String val = "";
                                    if (!StringUtils.isEmpty(date)){
                                        val = DateUtils.formatDate(date,"yyyy-MM-dd");
                                    }
                                    run.setText(val);
                                    break;
                                }
                                case 6:{
                                    String val = "";
                                    Integer nationId = internship.getNation();
                                    if(!StringUtils.isEmpty(nationId)){
                                        val = nationService.getById(nationId).getNationName();
                                    }
                                    run.setText(val);
                                    break;
                                }
                                case 7:{
                                    String val = "";
                                    Integer politicsStatusId = internship.getPoliticsStatus();
                                    if(!StringUtils.isEmpty(politicsStatusId)){
                                        val = politicsStatusService.getById(politicsStatusId).getPoliticsName();
                                    }
                                    run.setText(val);
                                    break;
                                }
                                case 8:{
                                    run.setText(internship.getOpenAccount());
                                    break;
                                }
                                case 9:{
                                    run.setText(internship.getBankAccount());
                                    break;
                                }
                                case 10:{
                                    Date date = internship.getStartDate();
                                    String val = "";
                                    if (!StringUtils.isEmpty(date)){
                                        val = DateUtils.formatDate(date,"yyyy-MM-dd");
                                    }
                                    run.setText(val);
                                    break;
                                }
                                case 11:{
                                    Date date = internship.getEndDate();
                                    String val = "";
                                    if (!StringUtils.isEmpty(date)){
                                        val = DateUtils.formatDate(date,"yyyy-MM-dd");
                                    }
                                    run.setText(val);
                                    break;
                                }
                                case 12:{
                                    run.setText(internship.getEmergencyContact());
                                    break;
                                }
                                case 13:{
                                    run.setText(internship.getEmergencyPhone());
                                    break;
                                }
                                case 14:{
                                    run.setText(internship.getEmergencyEmail());
                                    break;
                                }
                                case 15:{
                                    run.setText(internship.getPhone());
                                    break;
                                }
                                case 16:{
                                    run.setText(internship.getQq());
                                    break;
                                }
                                case 17:{
                                    run.setText(internship.getVx());
                                    break;
                                }
                                case 18:{
                                    run.setText(internship.getWiredMAC());
                                    break;
                                }
                                case 19:{
                                    run.setText(internship.getWirelessMAC());
                                    break;
                                }
                                case 20:{
                                    run.setText(internship.getNativePlace());
                                    break;
                                }
                                case 21:{
                                    run.setText(internship.getAddress());
                                    break;
                                }
                                case 22:{
                                    run.setText(internship.getSchool());
                                    break;
                                }
                                case 23:{
                                    String val = "";
                                    Integer educationId = internship.getEducation();
                                    if(!StringUtils.isEmpty(educationId)){
                                        val = educationService.getById(educationId).getEducationName();
                                    }
                                    run.setText(val);
                                    break;
                                }
                            }

                            count++;
                        }
                    }
                }
            }
        }
        OutputStream os = new FileOutputStream("D:\\upFiles\\words\\"+internship.getDouyin()+".doc");
        doc.write(os);
        if(os!=null){
            try {
                os.close();
                System.out.println("文件已输出！");
            } catch(IOException e){
                e.printStackTrace();
            }
        }

        return "words\\"+internship.getDouyin()+".doc";
    }
}
